# -*- coding: utf-8 -*-

from flask import request, render_template, jsonify, flash, url_for, session, redirect

from flask.ext.openid import OpenID
from flask.ext.wtf import Form
from flask_wtf.csrf import CsrfProtect
from flask.ext.login import login_user, logout_user, login_required, current_user

from wtforms import StringField, PasswordField, IntegerField, BooleanField, SubmitField, RadioField, SelectMultipleField
from wtforms.validators import Required, Length, Email

import datetime, calendar

from flask.ext import excel
import pyexcel.ext.xls

import os, json, sys

from functools import wraps

from app import app, db, login_manager

from .models import Teacher, Student, HistoryRecord, ChattingRecord, SubsContent, SubsTime
from .forms import LoginForm, StuCommitForm

from .utilities import DateAndTimeHandler, SmsHandler, NjuUtility 

from config import ALLOWED_EXTENSIONS
from config_user import ROLE_ADMIN,ROLE_TEACHER,DISTRICT_GULOU,DISTRICT_XIANLIN,TCH_ONLINE,TCH_WORKING
from config_user import CONSULT_TODO, CONSULT_AGAIN, CONSULT_COMPLETE, CONSULT_CANCEL, INVALID_TAG
from config_user import STU_INSTANT, STU_NORMAL, STU_BLACK, STU_INIT

CANCEL_TO_BLACK=3

#辅助及回调函数区
def __OutDebugInfo(a,*b):
    return
    sys.stdout.write(a)
    for x in b:
        sys.stdout.write(x)
        
    print('')
    return

#载入用户回调函数   
@login_manager.user_loader
def load_user(user_id):
    return Teacher.query.get(int(user_id))

#自定义学生用户登录验证
def stu_login_user(session, user):
    session['stu_logged_in'] = True
    session['stu_user_id'] = user.id
    session['stu_contact'] = user.contact

def stu_logout_user(session, user):
    session.pop('stu_logged_in',None)
    session.pop('stu_user_id',None)
    session.pop('stu_contact',None)

def stu_login_required(view):
    @wraps(view)
    def wrapper(*args, **kwargs):
        if 'stu_logged_in' in session:
            return view(*args, **kwargs)
        else:
            #flash('You need to login first.')
            return redirect(url_for('register_and_login',next=request.url))
    return wrapper

@app.route('/secret')
@login_required
def secret():
    return 'Only authenticated users are allowed!'

#主页
@app.route('/', methods=['GET'])
@app.route('/index', methods=['GET', 'POST'])
def index():
    return render_template('index_old.html',form=Form())
    
@app.route('/login_plat', methods=['GET', 'POST'])
def login_plat():
    return render_template('login.html',form=Form())

#主页-进入咨询师排班表
@app.route('/enter_scheduling', methods=['GET', 'POST'])
def enter_scheduling():
    return render_template('enter_scheduling.html')

@app.route('/register_and_login', methods=['GET','POST'])
def register_and_login():
    contact = request.form['contact']
    password = request.form['password']

    stu = Student.query.filter_by(contact=contact).first()

    msg = ""
    status = "ERROR"
    ret = True
    needvery = False

    if stu is not None:
        if stu.verify_password(password):
            status = "OK"
            
            if stu.status == STU_INSTANT:
                needvery = True
            
        else:
            msg = "您输入的密码有误"
            ret = False
    else:
        msg = "初次登陆系统，为您创建账号：" + str(contact)
        try:
            mem = Student("name:"+str(contact), "None", contact, password, "ID:"+str(contact), "email:"+str(contact), "", "", "", 0, STU_INSTANT)
            db.session.add(mem)
            db.session.commit()
            status = "FIR"
            needvery = True
            
            #flash(msg)
            # TODO:验证电话号码
            
        except Exception as e:
            msg = "初次登陆系统，创建账号" + str(contact) + "失败： " + str(e)
            ret = False

    if ret:
        stu_user = Student.query.filter_by(contact=contact).first()
        if stu_user is None:
            status = "ERROR"
        else:
            stu_login_user(session, stu_user)

    return jsonify(status=status,msg=msg,needvery=needvery)

@app.route('/verify_contact', methods=['GET','POST'])
@stu_login_required
def verify_contact():
    form = LoginForm()
    contact = form.contact.data
    verycode = form.password.data
    
    status = "OK"
    if verycode != session['very_code']:
        status = "ERROR"
    else:
        stu_user = Student.query.filter_by(contact=contact).first()
        stu_user.status = STU_INIT
        db.session.add(stu_user)
        db.session.commit()
        
    return jsonify(status=status,msg="")
    
@app.route('/get_verify_code', methods=['GET','POST'])
@stu_login_required
def get_verify_code():
    form = LoginForm()
    contact = form.contact.data
    #print(contact)
    status = "OK"
    msg = ""
    if contact != session['stu_contact']:
        status = "ERROR"
        msg = "服务器发生错误，请重新登陆！"
        return jsonify(status=status,msg=msg)
    
    veryc = SmsHandler.createVerifyCode()
    session['very_code'] = veryc
    #print(veryc)
    
    #发送登陆验证码，模板号：387837
    sendres = SmsHandler.SendSmsMsg(contact, veryc, 387837)
    #print(sendres)
    
    #解析返回码
    #if sendres["stat"] != "100":
    #    status = "ERROR"
    #    msg = "服务器发送验证码失败，请重新操作！"
    
    return jsonify(status=status,msg=msg)

@app.route('/get_registed_info', methods=['GET','POST'])
@stu_login_required
def get_registed_info():
    contact = session['stu_contact']
    stu_user = Student.query.filter_by(contact=contact).first()

    msg = ""

    if stu_user is None:
        msg = "用户" + str(contact) + "不存在，请重新注册!"
        return jsonify(status="ERROR",msg=msg)

    info = []

    if stu_user.status!=STU_INSTANT and stu_user.status!=STU_INIT:
        li = {}
        li['name'] = stu_user.name
        li['sex'] = stu_user.sex
        li['department'] = stu_user.department
        li['major'] = stu_user.major
        li['grade'] = stu_user.grade
        li['stuid'] = stu_user.stuID

        info.append(li)

        return jsonify(status="INFO",msg=msg, info=info)
        
    else:
        msg = "用户尚未预约任何咨询师，如需请前往预约界面进行操作！"
        return jsonify(status="NONE",msg=msg)
        

#主页-校内预约信息
@app.route('/teacher_info_campus', methods=['GET', 'POST'])
@stu_login_required
def teacher_info_campus():
    __OutDebugInfo("begin teacher_info_campus")
    
    teacher = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    
    lt = []
    gulou_lt = []
    msg = ""

    now = datetime.datetime.now()
    weeknum = int(now.weekday())  #表示星期几，从0开始
    
    kyydate = DateAndTimeHandler().GetKYYInterval()
    
    #获取最新数据显示到页面
    if teacher is not None:
        for item in teacher:
            dt = {}
            
            #(dateseq,timeseq) = item.date.split(':')
            dateseq = item.wkd_id
            timeseq = item.stm_id
            
            int_dateseq = 0
            try:
                int_dateseq = int(dateseq)
            except:
                msg = "未知错误，请联系管理员处理"
                stu_logout_user(session, current_user)
                return render_template('msg_handler.html',msg=msg)

            #if (weeknum+1)>int_dateseq or ((weeknum+1)==int_dateseq and (str(now.time())>'12:00:00')): #当前预约已错过时效
                #continue
            
            dt['name'] = item.name
            dt['dateseq'] = DateAndTimeHandler().toDateSeqStr(dateseq)
            dt['timeseq'] = timeseq#DateAndTimeHandler().toTimeSeqStr(timeseq)
            dt['isboot'] = item.isboot
            dt['href'] = item.href
            
            strpb = "<td class=\"kyy\" id = \"" + item.name + "\" dateseq=\"" + DateAndTimeHandler().toDateSeqStr(dateseq) + "\" timeseq=\"" + \
                    timeseq + "\">" + timeseq + "</td>"
            strpbRes = ""
            today = datetime.datetime.now().weekday()
            yyforward = int_dateseq>(today+1) or today==calendar.SATURDAY or today==calendar.SUNDAY #只能提前一天预约本周的信息
            for i in range(5): #每周5天
                if (i+1)==int_dateseq and not item.isboot: # and yyforward:
                    strpbRes += strpb
                else:
                    strpbRes += "<td></td>"

            dt['jspb'] = strpbRes
            dt['begintime'] = timeseq
            
            if item.position == DISTRICT_XIANLIN:
                lt.append(dt)
            else:
                gulou_lt.append(dt)
                
    else: #数据库中无咨询老师记录
        msg = "无咨询老师信息，请联系管理员！"
        return render_template('msg_handler.html',msg=msg)

    return render_template('teacher_info_campus.html', form=Form(), teachers=lt, gulou_teachers=gulou_lt, kyydate=kyydate)

#在线咨询处理
@app.route('/chatting', methods=['GET', 'POST'])
@stu_login_required
def chatting():
    teacher = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    
    lt = []
    
    #获取最新数据显示到页面
    if teacher is not None:
        for item in teacher:
            if item.status==TCH_ONLINE or item.status==TCH_WORKING:
                dt = {}
            
                dt['name'] = item.name
                dt['href'] = item.href
                dt['id'] = item.id
                dt['opt'] = False
                dt['status'] = "忙碌"
                if item.status==TCH_ONLINE:
                    dt['opt'] = True
                    dt['status'] = "在线"
                lt.append(dt)
                
    else: #数据库中无咨询老师记录
        msg = "无咨询老师信息，请联系管理员！"
        return render_template('msg_handler.html',msg=msg)
        
    return render_template('chatting.html', form=Form(), teachers=lt)

@app.route('/get_chat_msg', methods=['GET', 'POST'])
@stu_login_required
def get_chat_msg():
    if request.method == 'GET':
        a = request.args.get('a', 0, type=int)
        tcid = request.args.get('tcid', 0, type=int)
        stid = request.args.get('stid', 0, type=int)
        #print("a:%d, tcid:%d, stid:%d" % (a, tcid, stid))
        dt = {}
        rec = []
        stuid = 0
        
        if a==0:
            contact = session['stu_contact']
            stu_user = Student.query.filter_by(contact=contact).first()
            stuid = stu_user.id
            
            teacher = Teacher.query.filter_by(id=tcid).first()
            if teacher is not None:
                teacher.status = TCH_WORKING
                db.session.add(teacher)
                db.session.commit()
            return jsonify(status="OK",stuid=stuid)
        elif a==1:
            chatrecs = ChattingRecord.query.filter_by(unread=True).all()
            if chatrecs is not None:
                for item in chatrecs:
                    if item.totch or item.tch_id!=tcid or item.stu_id!=stid:
                        continue
                    item.unread = False
                    db.session.add(item)
                    db.session.commit()
                    teacher = Teacher.query.filter_by(id=tcid).first()
                    if teacher is not None:
                        dt['tcname'] = teacher.name
                    dt['msg'] = item.msg
                    dt['time'] = item.timestamp.strftime("%H:%M:%S")
                    rec.append(dt)
            else:
                pass
                    
            return jsonify(status="OK",rec=rec)
    elif request.method == 'POST':
        msg = request.form.get('toserver')
         
    return jsonify(status="OK",stuid=stuid)
        
@app.route('/get_stu_chat_msg', methods=['GET', 'POST'])
@login_required
def get_stu_chat_msg():
    if request.method == 'GET':
        a = request.args.get('a', 0, type=int)
        tcid = request.args.get('tcid', 0, type=int)
        #stid = request.args.get('stid', 0, type=int)
        #print("a:%d,tcid:%d" % (a,tcid))
        dt = {}
        rec = []
        if a==1:
            chatrecs = ChattingRecord.query.filter_by(unread=True).all()
            if chatrecs is not None:
                for item in chatrecs:
                    if not item.totch or item.tch_id!=tcid:
                        continue
                    item.unread = False
                    db.session.add(item)
                    db.session.commit()
                    dt['msg'] = item.msg
                    dt['stid'] = item.stu_id
                    dt['time'] = item.timestamp.strftime("%H:%M:%S")
                    stu = Student.query.filter_by(id=item.stu_id).first()
                    dt['stname'] = stu.name
                    rec.append(dt)
                    
        return jsonify(rec=rec)
    
    return jsonify(status="OK")
        
@app.route('/put_stu_chat_msg', methods=['GET', 'POST'])
@stu_login_required
def put_stu_chat_msg():
    tcid = request.form.get('tcid')
    stid = request.form.get('stid')
    msg = request.form.get('toserver')
    status="OK"
    try:
        chatrec = ChattingRecord(tcid,stid,msg,datetime.datetime.now())
        if chatrec is not None:
            db.session.add(chatrec)
            db.session.commit()
    except Exception as e:
        status="ERROR"
        print(e)
        pass
    
    return jsonify(status=status)

@app.route('/put_tch_chat_msg', methods=['GET', 'POST'])
@login_required
def put_tch_chat_msg():
    tcid = request.form.get('tcid')
    stid = request.form.get('stid')
    msg = request.form.get('toserver')
    totch = False
    status = "OK"
    
    try:
        chatrec = ChattingRecord(tcid,stid,msg,datetime.datetime.now(),totch)
        if chatrec is not None:
            db.session.add(chatrec)
            db.session.commit()
    except Exception as e:
        status = "ERROR"
        print(e)
        pass
    
    return jsonify(status=status)

#预约并注册记录学生信息
@app.route('/regester_data', methods=['GET', 'POST'])
@stu_login_required
def regester_data():
    __OutDebugInfo("regester_data:预约并注册记录学生信息")
    form = StuCommitForm()

    contact = session['stu_contact']
    if form.validate_on_submit():
        stu = Student.query.filter_by(contact=contact).first()
    else:
        msg = "请将注册信息填写完整！"
        return render_template('msg_handler.html',msg=msg)

    name = form.name.data
    sex = form.sex.data
    
    department = form.department.data
    major = form.major.data
    grade = form.grade.data
    stuid = form.stuid.data
    email = stu.email #form.email.data
    pw = stu.password
    tcname = form.tcid.data
    content = form.content.data
    demands = form.demands.data
    
    exist = Student.query.filter_by(contact=contact).first()

    msg = ""
    status = "0"  #表示该用户无预约信息
    if exist is None:
        msg = "未知错误"
        status = "2"
    
    else:       
        status = "1"
        
        if exist.currecid != INVALID_TAG:
            msg = "您已存在预约信息，请在完成咨询之后再进行新的预约！"
            return render_template('msg_handler.html',msg=msg)
        elif exist.status == STU_BLACK:
            msg = "您已被限制预约权限，请致电就业中心89687519，83597519，联系管理员处理！"
            return render_template('msg_handler.html',msg=msg)

        exist.name = name
        exist.sex = sex
        exist.department = department
        exist.major = major
        exist.grade = grade
        exist.stuID = stuid

        tch = Teacher.query.filter_by(name=tcname).first()
        if tch is not None and not tch.isboot:
            tch.isboot = True
            exist.tch_id = tch.id
            exist.status = STU_NORMAL
            
            yytime = DateAndTimeHandler().GetYYDateAndTime(tch.wkd_id)
            yytime = yytime+'-'+tch.stm_id
            strcont = u""
            for item in content:
                subcont = SubsContent.query.filter_by(id=int(item)).first()
                if subcont is not None:
                    strcont = strcont + subcont.content + u";"

            hisrec = HistoryRecord(tch.id, tch.name, exist.id, exist.name, yytime, CONSULT_TODO, strcont, demands)
            if hisrec is not None:                
                db.session.add(hisrec)
                db.session.commit()
                exist.currecid = hisrec.id
                tch.currecid = hisrec.id
                
            db.session.add(exist)
            db.session.add(tch) #更新咨询师表中预约标志
            
            db.session.commit()
            
            if tch.position == DISTRICT_GULOU:
                tostu = SmsHandler.SendSmsMsg(contact, yytime+tch.name+"咨询老师", 387203)
                msg = "您已预约了" + yytime + tch.name + "咨询老师。" + "请按照预约信息提前5分钟到达鼓楼南园教学楼2楼西侧就业事务办理厅（222），等候咨询师。"
            elif tch.position == DISTRICT_XIANLIN:
                # 给老师和学生发送预定信息通知短信
                tostu = SmsHandler.SendSmsMsg(contact, yytime+tch.name+"咨询老师", 390079)
                msg = "您已预约了" + yytime + tch.name + "咨询老师。" + "请按照预约信息提前5分钟到达仙林校区第10食堂3楼学生就业事务办理厅（326），等候咨询师。"
                      
            #给教师发送短信的模板号：
            #totch = SmsHandler.SendSmsMsg(tch.contact, exist.name + "已经预约您" + yytime + "时段的咨询", 388015)
            totch = SmsHandler.SendSmsMsg(tch.contact, exist.name + "已经预约您" + yytime + "时段的咨询", 388015)
        elif tch is None:
            msg = "无此老师信息，请刷新后重新预约！"
            status = "2"
        elif tch.isboot:
            msg = "非常抱歉，该老师已被他人预约，请刷新咨询排班信息！"
            status = "3"
        else:
            msg = "发生了未知错误"
            status = "4"

    stu_logout_user(session, exist)

    return render_template('msg_handler.html',msg=msg)

@app.route('/online_regester_data', methods=['GET', 'POST'])
@stu_login_required
def online_regester_data():
    form = StuCommitForm()

    contact = session['stu_contact']
    stu = None
    if form.validate_on_submit():
        stu = Student.query.filter_by(contact=contact).first()
    else:
        msg = "请将注册信息填写完整！"
        return render_template('msg_handler.html',msg=msg)

    name = form.name.data
    sex = form.sex.data
    
    department = form.department.data
    major = form.major.data
    grade = form.grade.data
    
    if stu is not None:
        stu.name = name
        stu.sex = sex
        stu.department = department
        stu.major = major
        stu.grade = grade
        stu.status = STU_INIT
        db.session.add(stu)
        db.session.commit()
        
    return jsonify(status="OK")

@app.route('/stu_online_logout', methods=['GET', 'POST'])
@stu_login_required
def stu_online_logout():
    contact = session['stu_contact']
    stu = Student.query.filter_by(contact=contact).first()
    stu_logout_user(session, stu)
    
    return render_template('online_acquire.html')   
    
@app.route('/stu_cancel_logout', methods=['GET', 'POST'])
@stu_login_required
def stu_cancel_logout():
    contact = session['stu_contact']
    stu = Student.query.filter_by(contact=contact).first()
    stu_logout_user(session, stu)
    
    return render_template('cancel_page.html',form=LoginForm()) 
    
@app.route('/stu_logout', methods=['GET', 'POST'])
@stu_login_required
def stu_logout():
    contact = session['stu_contact']
    stu = Student.query.filter_by(contact=contact).first()
    stu_logout_user(session, stu)
    
    return render_template('enter_scheduling.html')   

#主页-学生用户取消预约
@app.route('/cancel_page', methods=['GET', 'POST'])
def cancel_page():
    return render_template('cancel_page.html', form=LoginForm())

@app.route('/query_and_cancel', methods=['GET', 'POST'])
def query_and_cancel():
    form = LoginForm()
    if form.validate_on_submit():
        contact = form.contact.data
        password = form.password.data

        stu = Student.query.filter_by(contact=contact).first()

        msg = ""

        if stu is None:
            msg = "无此账号，请先前往预约页面注册账号！"
            flash(msg)
            return render_template('cancel_page.html', form=form)
        else:
            if stu.verify_password(password):
                stu_login_user(session, stu)
                hisrec = HistoryRecord.query.filter_by(stu_id=stu.id).all()
                hasBoot = False
                l = []
                if hisrec is not None:
                    for item in hisrec: 
                        di = {}
                        di['seq'] = item.id
                        di['date'] = item.date
                        di['status'] = NjuUtility.GetYYStatus(item.status)
                        di['tchname'] = item.tch_name
                        di['content'] = item.content
                        di['demands'] = item.demands
                        di['feedback'] = item.feedback
                        di['boottime'] = item.timestamp.strftime("%H:%M:%S")
                        if stu.currecid == item.id:
                            di['cancel'] = True
                            hasBoot = True
                        else:
                            di['cancel'] = False
                        tch = Teacher.query.filter_by(id=item.tch_id).first()
                        di['href'] = tch.href
                        di['pos'] = "仙林校区"
                        if tch.position == DISTRICT_GULOU:
                            di['pos'] = "鼓楼校区"
                        l.append(di)
                        
                return render_template('cancel_book.html', form=form, lists=l, hasBoot=hasBoot, cont=contact, name=stu.name)
            else:
                msg = "您输入的密码有误，请核对后重新输入！"
                flash(msg)
                return render_template('cancel_page.html', form=form)
    else:
        flash("请填写正确的电话号码和密码")
        return render_template('cancel_page.html', form=form)

@app.route('/cancel_book', methods=['GET','POST'])
@stu_login_required
def cancel_book():
    form = LoginForm()
    contact = session['stu_contact']
    
    verycode = form.contact.data
    
    status = "OK"
    ret = False
    #print(verycode)
    
    #print(session['very_code'])
    if verycode != session['very_code']:
        status = "ERROR"
        msg = "您输入的验证码不正确，请重新输入"
        return jsonify(status=status,msg=msg,ret=ret)
    
    stu = Student.query.filter_by(contact=contact).first()
    msg = ""
    status = "ERROR"
    
    if stu is not None:
        hisrec = HistoryRecord.query.filter_by(id=stu.currecid).first()
        if hisrec is not None:
            sendmsgtostu = None
            sendmsgtotch = None
            tch = Teacher.query.filter_by(id=hisrec.tch_id).first()
            if tch is not None and tch.isboot:
                tch.isboot = False
                tch.currecid = INVALID_TAG
                db.session.add(tch)
            
                msg = "您已成功取消预约信息"
                yytime = DateAndTimeHandler().GetYYDateAndTime(tch.wkd_id)
                yytime = yytime + " " + tch.stm_id
                strpos = "仙林校区"
                if tch.position == DISTRICT_GULOU:
                    strpos = "鼓楼校区"
                sendmsgtostu = "您已取消"+yytime+strpos+tch.name+"预约信息"
                sendmsgtotch = stu.name + "已经取消您" + yytime + "的咨询预约"

                stu.currecid = INVALID_TAG  #表示删除当前预约老师信息
                stu.canceltimes = stu.canceltimes+1
                if stu.canceltimes > CANCEL_TO_BLACK:
                    stu.status = STU_BLACK
                    msg = "由于您取消次数过多，三个月内您可能将无权进行预约！"
                db.session.add(stu)
                
                hisrec.status = CONSULT_CANCEL
                hisrec.timestamp = datetime.datetime.now()
                db.session.add(hisrec)
                
                db.session.commit()
                
                # 给老师和学生发送取消预定信息通知短信
                tostu = SmsHandler.SendSmsMsg(contact, sendmsgtostu, 388170)
                #给教师发送取消预约短信的模板号：万能模板号：388170
                if tch is not None:
                    totch = SmsHandler.SendSmsMsg(tch.contact, sendmsgtotch, 388170)
                
                status = "OK"
                ret = True
            elif tch is None:
                msg = "该用户无预约信息，请联系管理员处理！"
                status = "ERROR"
            else:
                msg = "该用户无预约信息，请联系管理员处理！"
                status = "ERROR"
        else:
            status = "ERROR"
            msg = "未知错误！"
    else:
        status = "ERROR"
        msg = "用户不存在，注册预约成功才可以取消预约！"

    return jsonify(status=status,msg=msg,ret=ret)

#主页-教师入口
@app.route('/admin_teacher', methods=['GET', 'POST'])
def admin_teacher():
    form = LoginForm()
    return render_template('admin_teacher.html', form=form)

#管理员操作

# 1.添加教师
@app.route('/regester_teacher_data', methods=['GET', 'POST'])
@login_required
def regester_teacher_data():
    name = request.form['a']
    contact = request.form['b']
    dateseq = request.form['c']
    timeseq = request.form['d']
    email = request.form['e']
    password = request.form['f']
    href = request.form['g']
    pos = request.form['h']

    msg = ""
    result = "1"

    tch = Teacher.query.filter_by(contact=contact).first()
    if tch is not None:
        msg = "注册号码" + contact + "已存在！"
    else:
        tc_pos = DISTRICT_XIANLIN
        if str(pos) == "2":
            tc_pos = DISTRICT_GULOU
        new_tch = Teacher(name,contact,email,password,dateseq,timeseq,href,tc_pos)
        db.session.add(new_tch)
        db.session.commit()

        msg = "已成功添加咨询师：" + name
        result = "0"
            
    return jsonify(result=result,msg=msg)

# 2.查看教师
@app.route('/view_tch_data', methods=['GET', 'POST'])
@login_required
def view_tch_data():
    __OutDebugInfo("view_tch_data")
    tchs = Teacher.query.filter_by(role=ROLE_TEACHER).all()
    li_ = []
    if tchs is not None:
        for item in tchs:
            di = {}
            di['name']=item.name
            di['contact'] = item.contact
            di['email'] = item.email
            di['dateseq'] = DateAndTimeHandler().toDateSeqStr(item.wkd_id)
            di['timeseq'] = item.stm_id
            di['isboot'] = item.isboot
            di['pos'] = item.position

            sub_name = "无"
            if item.isboot:
                hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
                if hisrec is not None:
                    msg = "sucess"
                    sub_name = hisrec.stu_name
                else:
                    msg = "server internal error"

            di['subname'] = sub_name
            li_.append(di)
 
    return json.dumps(li_)

# 3.删除咨询师用户
@app.route('/delete_teacher', methods=['GET', 'POST'])
@login_required
def delete_teacher():
    cont = request.form['contact']
    tch = Teacher.query.filter_by(contact=cont).first()
    msg = ""
    name = ""

    if tch is not None:

        if tch.isboot:
            msg="您要删除的咨询老师已经被预约，请先取消学生预约信息再删除！"
        else:
            name = tch.name
            db.session.delete(tch)
            db.session.commit()
            msg = "成功删除教师用户：" + name
    else:
        msg = "您要删除的用户不存在！"
    
    return jsonify(result="", msg=msg)

# 4.显示学生用户
@app.route('/view_stu_data', methods=['GET', 'POST'])
@login_required
def view_stu_data():
    __OutDebugInfo("view_stu_data")
    stus = Student.query.all()
    li_ = []
    if stus is not None:
        for item in stus:
            di = {}
            di['name']=item.name
            di['sex']=item.sex
            di['contact'] = item.contact
            #di['email'] = item.email
            di['department'] = item.department
            di['major'] = item.major
            di['grade'] = item.grade
            di['stuID'] = item.stuID
            di['status'] = item.status
            di['cans'] = item.canceltimes
    
            hisrec = HistoryRecord.query.filter_by(id=item.currecid).first()
            if hisrec is not None:
                di['date'] = hisrec.date
                di['tchname'] = hisrec.tch_name
            else:
                di['date'] = "-"
                di['tchname'] = "无"
        
            li_.append(di)

    return json.dumps(li_)
    
@app.route('/view_rec_data', methods=['GET', 'POST'])
@login_required
def view_rec_data():
    __OutDebugInfo("view_rec_data")

    hisrec = HistoryRecord.query.all()
    li_ = []
    if hisrec is not None:
        for item in hisrec:
            di = {}
            di['seq'] = item.id
            di['tchname']=item.tch_name
            di['tchid']=item.tch_id
            di['stuname']=item.stu_name
            di['stuid']=item.stu_id
            di['date'] = item.date
            di['status'] = NjuUtility.GetYYStatus(item.status)
            di['content'] = item.content
            di['demands'] = item.demands
            di['feedback'] = item.feedback
            di['timestamp'] = item.timestamp.strftime("%H:%M:%S")
        
            li_.append(di)

    return json.dumps(li_)

@app.route('/add_time_opt', methods=['GET', 'POST'])
@login_required
def add_time_opt():
    stm = request.form['stm']
    
    ret = False
    try:
        if stm is not None and stm!="":
            subtime = SubsTime(stm)
            db.session.add(subtime)
            db.session.commit()
            ret = True
    except:
        pass
        
    return jsonify(ret=ret)

@app.route('/add_cont_opt', methods=['GET', 'POST'])
@login_required
def add_cont_opt():
    cont = request.form['cont']
    
    ret = False
    try:
        if cont is not None and cont!="":
            subcont = SubsContent(cont)
            db.session.add(subcont)
            db.session.commit()
            ret = True
    except:
        pass
        
    return jsonify(ret=ret)

@app.route('/del_time_opt', methods=['GET', 'POST'])
@login_required
def del_time_opt():
    stm = request.form['stm']
    
    ret = False
    subtime = SubsTime.query.filter_by(stm=stm).first()
    if subtime is not None:
        db.session.delete(subtime)
        db.session.commit()
        ret = True
        
    return jsonify(ret=ret)

@app.route('/del_cont_opt', methods=['GET', 'POST'])
@login_required
def del_cont_opt():
    cont = request.form['cont']
    
    ret = False
    subcont = SubsContent.query.filter_by(content=cont).first()
    if subcont is not None:
        db.session.delete(subcont)
        db.session.commit()
        ret = True
    
    return jsonify(ret=ret)

@app.route('/existed_cont', methods=['GET', 'POST'])
@login_required
def existed_cont():
    subcont = SubsContent.query.all()
    l = []
    if subcont is not None:
        for item in subcont:
            di = {}
            di['id'] = item.id
            di['cont'] = item.content
            l.append(di)
            
    return json.dumps(l)

@app.route('/existed_time', methods=['GET', 'POST'])
@login_required
def existed_time():
    subtimes = SubsTime.query.all()
    l = []
    if subtimes is not None:
        for item in subtimes:
            di = {}
            di['id'] = item.id
            di['stm'] = item.stm
            l.append(di)
            
    return json.dumps(l)
 
@app.route('/modify_tch_pb', methods=['GET', 'POST'])
@login_required
def modify_tch_pb(): 
    contact = request.form['contact']
    wkd = request.form['wkd']
    stm = request.form['stm']
    
    ret = False
    tch = Teacher.query.filter_by(contact=contact).first()
    if tch is not None:
        tch.wkd_id = wkd
        tch.stm_id = stm
        db.session.add(tch)
        db.session.commit()
        ret = True

    return jsonify(ret=ret)

# 4-1.咨询老师查看预约自己的学生
@app.route('/view_spec_stu_data', methods=['GET', 'POST'])
def view_spec_stu_data():
    tcid = request.form['a']
    
    li_ = []
    hisrecs = HistoryRecord.query.filter_by(tch_id=tcid).all()
    if hisrecs is not None:
        for item in hisrecs:
            di = {}
            di['recid'] = item.id
            di['name']=item.stu_name
            di['stuno']=item.stu_id
            di['status'] = item.status
            di['date'] = item.date
            di['content'] = item.content
            di['demands'] = item.demands
            di['feedback'] = item.feedback
            stu = Student.query.filter_by(id=item.stu_id).first()
            di['department'] = stu.department
            di['major'] = stu.major
            di['grade'] = stu.grade
            di['contact'] = stu.contact
            di['sex']=stu.sex
            li_.append(di)

    return json.dumps(li_)


# 5.删除学生用户
@app.route('/delete_student', methods=['GET', 'POST'])
@login_required
def delete_student():
    __OutDebugInfo("delete student")
    cont = request.form['contact']
    stu = Student.query.filter_by(contact=cont).first()

    msg = ""
    name = ""
    if stu is not None:
        name = stu.name

        if stu.currecid != INVALID_TAG: #已经预约成功，需要先取消预约信息
            tch = Teacher.query.filter_by(currecid=stu.currecid).first()
            if tch is not None:
                tch.isboot = False
                tch.currecid = INVALID_TAG
                db.session.add(tch)
            
        db.session.delete(stu)
        db.session.commit()
        msg = "成功删除学生用户：" + name
    else:
        msg = "您要删除的用户不存在！"

    return jsonify(result="", msg=msg)

# 6.咨询老师增加学生的反馈信息
@app.route('/add_stu_feedback', methods=['GET', 'POST'])
@login_required
def add_stu_feedback():
    cont = request.form['contact']
    fd = request.form['feedback']
    st = request.form['status']
    recid = request.form['recid']
    
    stu = Student.query.filter_by(contact=cont).first()
    
    msg = ""
    name = ""
    if stu is not None:
        name = stu.name
        
        hisrec = HistoryRecord.query.filter_by(id=recid).first()
        if hisrec is not None:
            st_int = hisrec.status
            try:
                if int(st) > CONSULT_CANCEL or int(st) < CONSULT_TODO:
                    pass
                else:
                    st_int = int(st)
            except:
                pass
            hisrec.status = st_int
            hisrec.feedback = fd
            hisrec.timestamp = datetime.datetime.now()
            db.session.add(hisrec)
            db.session.commit()
        msg = "成功添加学生用户" + name + "的反馈信息！"
    else:
        msg = "您所操作的用户不存在，请联系管理员处理！"

    return jsonify(result="", msg=msg)

#管理员修改学生的状态
@app.route('/set_stu_status', methods=['GET', 'POST'])
@login_required
def set_stu_status(): 
    cont = request.form['contact']
    st = request.form['status']
    
    stu = Student.query.filter_by(contact=cont).first()
    if stu is not None:
        st_int = stu.status
        try:
            if int(st) > STU_BLACK or int(st) < STU_INSTANT:
                pass
            else:
                st_int = int(st)
        except:
            pass
        stu.status = int(st)
            
        db.session.add(stu)
        db.session.commit()
        
    return jsonify(status="0", msg="OK")

#获取老师的服务状态
@app.route('/opt_tch_status', methods=['GET', 'POST'])
@login_required
def opt_tch_status():
    if request.method == 'GET':
        if current_user is not None:
            return jsonify(index=current_user.status)
    elif request.method == "POST":
        form = LoginForm()
        sta = form.contact.data
        if current_user is not None:
            tch = Teacher.query.filter_by(contact=current_user.contact).first()
            if tch is not None:
                tch.status = int(sta)
                db.session.add(tch)
                db.session.commit()
                
    return jsonify(status="OK")        

#管理员、咨询师登陆操作
@app.route('/login', methods=['GET', 'POST'])
def login():
    #import pdb; pdb.set_trace()
    form = LoginForm()
    msg = ""
    hasError = True
    
    if form.validate_on_submit():
        
        tch = Teacher.query.filter_by(contact=form.contact.data).first()

        if tch is not None:
            if tch.verify_password(form.password.data):
                login_user(tch)
                hasError = False
                if tch.role == ROLE_ADMIN:
                    return render_template('admin.html',form = form, name=tch.name)
                elif tch.role == ROLE_TEACHER:
                    return render_template('acquire.html',form = form, name=tch.name, tcid=tch.id)
            else:
                msg = "您输入的密码有误，请确认后重新输入"
        else:
            msg = "用户不存在，请联系管理员进行处理"
                    
    if hasError:
        flash(msg)
        return render_template('admin_teacher.html', form=form)
          
    return render_template('admin_teacher.html', form=form)

@app.route('/logout', methods=['GET', 'POST'])
@login_required
def logout():
    __OutDebugInfo("logout")
    logout_user()
    return render_template('admin_teacher.html', form=LoginForm())

@app.route('/msg_handler<msg>',  methods=['GET', 'POST'])
def msg_handler(msg):
    return render_template('msg_handler.html',msg=msg)
    
@app.route("/clear_yy_chat_records", methods=['GET', 'POST'])
@login_required
def clear_yy_chat_records():
    if current_user is not None:
        hisrec = HistoryRecord.query.all()
        for item in hisrec:
            stu = Student.query.filter_by(id=item.stu_id).first()
            if stu is not None and stu.currecid!=INVALID_TAG:
                stu.currecid = INVALID_TAG
                db.session.add(stu)
            tch = Teacher.query.filter_by(id=item.tch_id).first()
            if tch is not None:
                tch.isboot = False
                tch.currecid = INVALID_TAG
                db.session.add(tch)
            db.session.delete(item)
            
        chatting = ChattingRecord.query.all()
        for item in chatting:
            db.session.delete(item)
            
        db.session.commit()
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
        
    return render_template('admin_teacher.html', form=LoginForm())
    
@app.route("/import", methods=['GET', 'POST'])
@login_required
def doimport():
    if request.method == 'POST':
        try:
            def teacher_init_func(row):
                tch = Teacher(row['name'],row['contact'],row['email'],row['password'],row['date'],row['href'],row['position'],row['isboot'],row['role'])
                return tch

            def student_init_func(row):
                stu = Student(row['name'],row['sex'],row['contact'],row['password'],row['stuID'],row['email'],row['department'], \
                              row['major'],row['grade'],row['canceltimes'],row['status'])
                return stu
                
            def hisrec_init_func(row):
                his_rec = HistoryRecord(row['tch_id'],row['tch_name'],row['stu_id'],row['stu_name'],row['date'],row['status'],row['content'],row['timestamp'])
                
                return his_rec
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[Teacher,Student,HistoryRecord], initializers=[teacher_init_func,student_init_func,hisrec_init_func])
        except:
            pass
        return render_template('index_old.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import")

def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS   

@app.route("/import_tch", methods=['GET', 'POST'])
@login_required
def import_tch():
    if request.method == 'POST':
        """
        try:
            def teacher_init_func(row):
                tch = Teacher(row['姓名'],row['电话'],row['邮箱'],row['密码'],row['排班ID'],row['咨询时段'],row['个人链接'],row['校区ID'])
                #return None
                return tch
                
            request.save_to_database(field_name='file', session=db.session,
                table=Teacher, initializer=teacher_init_func)
        except Exception as e:
            return render_template('msg_handler.html',msg=str(e))
        """
        
        from pyexcel_xls import get_data
        from werkzeug import secure_filename
        try:
            file = request.files['file']
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                data = get_data(os.path.join(app.config['UPLOAD_FOLDER'], filename))

                ltop = data[0]
                noname = ltop.index('姓名')
                nocont = ltop.index('电话')
                noemail = ltop.index('邮箱')
                nopw = ltop.index('密码')
                nopbid = ltop.index('排班ID')
                notime = ltop.index('咨询时段')
                nohref = ltop.index('个人链接')
                nopos = ltop.index('校区ID')
                
                idx = 1
                hasnew = False
                while idx < len(data):
                    curls = data[idx]
                    cont = curls[nocont]

                    tch = Teacher.query.filter_by(contact=cont).first()
                    if tch is None:
                        tch = Teacher(curls[noname],curls[nocont],curls[noemail],curls[nopw],curls[nopbid],curls[notime],curls[nohref],curls[nopos])
                        db.session.add(tch)
                        hasnew = True
                    idx = idx+1
                
                if hasnew:
                    db.session.commit()
        except Exception as e:
            #print(e)
            return render_template('msg_handler.html',msg="导入文件格式可能有误，错误："+str(e))
        
        return render_template('admin.html',form = LoginForm(), name=current_user.name)
    
    return render_template('import.html',form=Form(),imadd="import_tch")
    
@app.route("/import_stu", methods=['GET', 'POST'])
@login_required
def import_stu():
    if request.method == 'POST':
        try:
            def student_init_func(row):
                stu = Student(row['name'],row['sex'],row['contact'],row['password'],row['stuID'],row['email'],row['department'], \
                              row['major'],row['grade'],row['canceltimes'],row['status'])
                return stu
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[Student], initializers=[student_init_func])
        except Exception as e:
            #print("exception happened")
            #print(e)
            pass
        return render_template('index_old.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import_stu")

@app.route("/import_yy", methods=['GET', 'POST'])
@login_required
def import_yy():
    if request.method == 'POST':
        try:               
            def hisrec_init_func(row):
                his_rec = HistoryRecord(row['tch_id'],row['tch_name'],row['stu_id'],row['stu_name'],row['date'],row['status'],row['content'],row['timestamp'])
                
                return his_rec
            
            request.save_book_to_database(field_name='file', session=db.session,
                tables=[HistoryRecord], initializers=[hisrec_init_func])
        except:
            pass
        return render_template('index_old.html',form=Form())
    
    return render_template('import.html',form=Form(),imadd="import_yy")

@app.route("/export", methods=['GET','POST'])
@login_required
def export():
    import pyexcel as pe
    import pyexcel_xls
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            #return excel.make_response_from_tables(db.session, [Teacher, Student, HistoryRecord], "xls")
            
            stulist = []
            stulist.append([u'序号',u'姓名',u'性别',u'电话',u'密码',u'学号',u'学院',u'专业',u'年级',u'状态',u'取消预约次数',u'预约记录序号'])
            stus = Student.query.all()
            if stus is not None:
                for item in stus:
                    l = []
                    l.append(item.id)
                    l.append(item.name)
                    l.append(item.sex)
                    l.append(item.contact)
                    l.append(item.password)
                    l.append(item.stuID)
                    l.append(item.department)
                    l.append(item.major)
                    l.append(item.grade)
                    l.append(NjuUtility.GetStuStatus(item.status))
                    l.append(item.canceltimes)
                    l.append(item.currecid)
                    stulist.append(l)
            
            yylist = []
            yylist.append([u'序号',u'学生',u'教师',u'咨询内容',u'咨询时间',u'需求',u'反馈',u'预约时间',u'状态'])
            recs = HistoryRecord.query.all()
            if recs is not None:
                for item in recs:
                    l = []
                    l.append(item.id)
                    l.append(item.stu_name)
                    l.append(item.tch_name)
                    l.append(item.content)
                    l.append(item.date)
                    l.append(item.demands)
                    l.append(item.feedback)
                    l.append(item.timestamp)
                    l.append(NjuUtility.GetYYStatus(item.status))
                    yylist.append(l)
                    
            sheet = pe.Sheet(stulist)
            sheet1 = pe.Sheet(yylist)
            sheets = OrderedDict()
            sheets['student_info'] = sheet
            sheets['booting_records'] = sheet1
            book = pe.Book(sheets)
            
            return excel.make_response(book, "xls", 200, 'student_and_booting_records')
                    
    return render_template('500.html'), 500
    
@app.route("/export_tch", methods=['GET','POST'])
@login_required
def export_tch():
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            query_sets = Teacher.query.filter_by(role=ROLE_TEACHER).all()
            ep = OrderedDict()
            name = []
            id = []
            cont = []
            date = []
            dateid = []
            time_ = []
            email = []
            href = []
            isboot = []
            pasw = []
            pos = []
            posid = []
                        
            for item in query_sets:
                id.append(item.id)
                name.append(item.name)
                cont.append(item.contact)
                date.append(DateAndTimeHandler().GetTchDateAndTime(item.wkd_id))
                dateid.append(item.wkd_id)
                time_.append(item.stm_id)
                email.append(item.email)
                href.append(item.href)
                if item.isboot:
                    isboot.append(u'是')
                else:
                    isboot.append(u'否')
                pasw.append(item.password)
                posid.append(item.position)
                if item.position==DISTRICT_GULOU:
                    pos.append(u"鼓楼校区")
                elif item.position==DISTRICT_XIANLIN:
                    pos.append(u"仙林校区")
                else:
                    pos.append(u"")
            
            hasele = len(query_sets)>0
            ep[u"序号"] = id
            ep[u"姓名"] = name
            ep[u"电话"] = cont
            if hasele:
                ep[u"排班"] = date
                ep[u"预约"] = isboot
            ep[u"邮箱"] = email
            ep[u"个人链接"] = href
            
            ep[u"密码"] = pasw
            if hasele:
                ep[u"所在校区"] = pos
            
            ep[u'排班ID'] = dateid
            ep[u'咨询时段'] = time_
            ep[u'校区ID'] = posid
            
            if not hasele:
                ep[u'排班ID'].append(u'排班id为数字:1到5代表周一到周五，如需输入，请删除本段文字并填入数字代码')
                ep[u'校区ID'].append(u'校区id栏，1代表鼓楼校区，2代表仙林校区，如需输入，请删除本段文字并填入数字代码')
                return excel.make_response_from_dict(ep, "xls", 200, "teacher_info_template")
                
            return excel.make_response_from_dict(ep, "xls", 200, "teacher_info")
    return render_template('500.html'), 500
   
@app.route("/export_template", methods=['GET','POST'])
@login_required
def export_template():
    from collections import OrderedDict
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            query_sets = Teacher.query.filter_by(role=ROLE_TEACHER).all()
            ep = OrderedDict()
            name = []
            id = []
            cont = []
            dateid = []
            email = []
            href = []
            pasw = []
            posid = []
                        
            ep[u"序号"] = id
            ep[u"姓名"] = name
            ep[u"电话"] = cont
            ep[u"邮箱"] = email
            ep[u"个人链接"] = href
            
            ep[u"密码"] = pasw
            
            ep[u'排班ID'] = dateid
            ep[u'校区ID'] = posid
            
            ep[u'排班ID'].append(u'排布id格式如1:1，冒号为英文输入格式下的，冒号:左边1到5代表周一到周五，右边1到4代表咨询时段，如需输入，请删除本段文字并填入数字代码，注意如1:1在单元格中向左对齐！谨防1:1被excel自动格式化为01:01:00！')
            ep[u'校区ID'].append(u'校区id，1代表鼓楼校区，2代表仙林校区，如需输入，请删除本段文字并填入数字代码')
            return excel.make_response_from_dict(ep, "xls", 200, "teacher_info_template")
                
    return render_template('500.html'), 500
        
@app.route("/export_stu", methods=['GET','POST'])
@login_required
def export_stu():
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            return excel.make_response_from_tables(db.session, [Student], "xls")
    return render_template('500.html'), 500
    
@app.route("/export_yy", methods=['GET','POST'])
@login_required
def export_yy():
    if current_user is not None:
        if current_user.role == ROLE_ADMIN:
            return excel.make_response_from_tables(db.session, [HistoryRecord], "xls")
    return render_template('500.html'), 500

# 错误处理
@app.errorhandler(404)
def not_found_error(error):
    return render_template('404.html'), 404

@app.errorhandler(500)
def internal_error(error):
    db.session.rollback()
    return render_template('500.html'), 500

